﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QuanLyBanHang.DataConnection;
using System.Windows.Forms;
using System.Data;

namespace QuanLyBanHang.QuerySQL
{
    class ClassPhieunhap
    {
        Connect conn = new Connect();
        //show phieu nhap
        public DataTable ShowPN(int i,string pn = null,string ncc = null)
        {
            DataTable tb = new DataTable(); ;
            switch(i){
                case 0:
                    {
                        try
                        {
                            string sql = "select MAPN,TENNCC,TENNV,NGAYLAP,TONGTIEN from PHIEUNHAP,NHACC,NHANVIEN where PHIEUNHAP.MANCC=NHACC.MANCC and PHIEUNHAP.MANV = NHANVIEN.MANV";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            string sql = "select MAPN,TENNCC,TENNV,NGAYLAP,TONGTIEN from PHIEUNHAP,NHACC,NHANVIEN where MAPN = '"+pn+"' and PHIEUNHAP.MANCC = '"+ncc+"' and PHIEUNHAP.MANCC=NHACC.MANCC and PHIEUNHAP.MANV = NHANVIEN.MANV";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 2:
                    {
                        try
                        {
                            string sql = "select MAPN from PHIEUNHAP";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
        }
            return tb;
        }
        //ktra chi tiết PN
        public DataTable ShowCTPN()
        {
            DataTable tb = new DataTable(); ;
            try
            {
                string sql = "select *from CHITIET_PN";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //show Mã NCC
        public DataTable ShowMANCC()
        {
            DataTable tb = new DataTable();
            try
            {
                string sql = "select MANCC from NHACC";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        // show tên sp và giá
        public DataTable Show_CTSP(int i, string masp = null)
        {
            DataTable tb = new DataTable();
            switch(i){
                case 0:
                    {
                        try
                        {
                            string sql = "select TENSP, GIABAN from SANPHAM where MASP = '" + masp + "' ";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            string sql = "select MASP from SANPHAM";
                            tb = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
        }
            return tb;
        }
        //thêm vào bảng phieu nhap
        public void ThemPN(string pn, string ncc, string nv)
        {
            try
            {
                string sql = "insert into PHIEUNHAP(MAPN,MANCC,MANV) values('" + pn+ "','" + ncc + "','" + nv + "')";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Nhập chi tiết đơn đặt hàng !!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        //thêm chi tiết hàng nhập
        public void Them_CTPN(string mapn, string masp, Double gia, Double slg, Double tong)
        {
            try
            {
                string sql = "insert into CHITIET_PN values('" + mapn + "','" + masp + "'," + gia + "," + slg + "," + tong + ")";
                conn.ExcuteQuerySql(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        //sửa giá
        public void SuaPN(string mapn, double tong)
        {
            try
            {
                string sql = "update PHIEUNHAP set TONGTIEN = " + tong + " where MAPN = '" + mapn + "'";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Thêm thành công, Cập nhậy sp !!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        //xoa
        public void XoaPN(string mapn)
        {
            try
            {
                string sql1 = "delete CHITIET_PN where MAPN = '" + mapn + "'";
                conn.ExcuteQuerySql(sql1);

                string sql2 = "delete PHIEUNHAP where MAPN = '" + mapn+ "'";
                conn.ExcuteQuerySql(sql2);

                MessageBox.Show("Xóa thành công !!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        public DataTable TKPN(int i, string mapn = null, string ncc = null)
        {
            DataTable dt = new DataTable();
            switch (i)
            {
                case 0:
                    {
                        try
                        {
                            String sql = "select MAPN,TENNCC,TENNV,NGAYLAP,TONGTIEN from PHIEUNHAP,NHACC,NHANVIEN where MAPN like N'%"+mapn+"%' and PHIEUNHAP.MANCC=NHACC.MANCC and PHIEUNHAP.MANV = NHANVIEN.MANV";
                            dt = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            String sql = "select MAPN,TENNCC,TENNV,NGAYLAP,TONGTIEN from PHIEUNHAP,NHACC,NHANVIEN where TENNCC like N'%"+ncc+"%' and PHIEUNHAP.MANCC=NHACC.MANCC and PHIEUNHAP.MANV = NHANVIEN.MANV";
                            dt = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
            }
            return dt;
        }
    }
}
